package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.ProductSKUDAO;
import com.fly.estore.domain.ProductInfo;
import com.fly.estore.domain.ProductSKU;
import org.apache.commons.io.output.StringBuilderWriter;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/25.
 */
@Repository("ProductSKUDAO")
public class ProductSKUDAOImpl extends BaseDAO<ProductSKU> implements ProductSKUDAO {

    public int addProductSKU(final ProductSKU productSKU) {
        int update = 0;
        StringBuilder sql = new StringBuilder("insert into product_sku(product_sku_id, product_id, sku_code, sku_name, sku_specs, sell_price, market_price, purchase_price)");
        sql.append("values(?, ?, ?, ?, ?, ?, ?, ?)");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, productSKU.getProductSkuId());
                ps.setString(i++, productSKU.getProductInfo().getProductId());
                ps.setString(i++, productSKU.getSkuCode());
                ps.setString(i++, productSKU.getSkuName());
                ps.setString(i++, productSKU.getSkuSpecs());
                ps.setFloat(i++, productSKU.getSellPrice());
                ps.setFloat(i++, productSKU.getMarketPrice());
                ps.setFloat(i++, productSKU.getPurchasePrice());
            }
        });
        return update;
    }

    public int updateProductSKU(final ProductSKU productSKU) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("update product_sku set")
                .append(" product_id = ?,")
                .append(" sku_code = ?,")
                .append(" sku_name = ?,")
                .append(" sku_specs = ?,")
                .append(" sell_price = ?,")
                .append(" market_price = ?,")
                .append(" purchase_price = ?")
                .append(" where product_sku_id = ?");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, productSKU.getProductInfo().getProductId());
                ps.setString(i++, productSKU.getSkuCode());
                ps.setString(i++, productSKU.getSkuName());
                ps.setString(i++, productSKU.getSkuSpecs());
                ps.setFloat(i++, productSKU.getSellPrice());
                ps.setFloat(i++, productSKU.getMarketPrice());
                ps.setFloat(i++, productSKU.getPurchasePrice());
                ps.setString(i++, productSKU.getProductSkuId());
            }
        });
        return update;
    }

    public int deleteById(final String productSkuId) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("delete from product_sku where product_sku_id = ?");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, productSkuId);
            }
        });
        return update;
    }

    public ProductSKU getById(String productSkuId) {
        ProductSKU productSKU= new ProductSKU();
//        String sql = "select ps.*, ifnull() from product_sku where product_sku_id = ?";
        StringBuilder sql = new StringBuilder();
        sql.append("select ps.*, ifnull(sd.stockAmount, 0) as stockAmount ")
                .append(" from product_sku ps ")
                .append(" left join (select sum(amount) as stockAmount, product_sku_id from stock_detail group by product_sku_id) sd on sd.product_sku_id = ps.product_sku_id ")
                .append(" where ps.product_sku_id = ?");
        List<ProductSKU> resultList = getJdbcTemplate().query(sql.toString(), new SKURowMapper(), new Object[]{productSkuId});
        if (!CollectionUtils.isEmpty(resultList)){
            productSKU = resultList.get(0);
        }
        return productSKU;
    }

    public List<ProductSKU> getByProductId(String productId) {
        List<ProductSKU> skuList = new ArrayList<ProductSKU>();
        StringBuilder sql = new StringBuilder();
        sql.append("select ps.*, ifnull(sd.stockAmount, 0) as stockAmount ")
                .append(" from product_sku ps ")
                .append(" left join (select sum(amount) as stockAmount, product_sku_id from stock_detail group by product_sku_id) sd on sd.product_sku_id = ps.product_sku_id ")
                .append(" where ps.product_id = ?");
        skuList = getJdbcTemplate().query(sql.toString(), new SKURowMapper(), new Object[]{productId});
        return skuList;
    }

    public class SKURowMapper implements RowMapper<ProductSKU>{
        public ProductSKU mapRow(ResultSet rs, int rowNum) throws SQLException {
            ProductSKU productSKU = new ProductSKU();
            ProductInfo productInfo = new ProductInfo();
            productSKU.setProductSkuId(rs.getString("product_sku_id"));
            productInfo.setProductId(rs.getString("product_id"));
            productSKU.setProductInfo(productInfo);
            productSKU.setSkuCode(rs.getString("sku_code"));
            productSKU.setSkuName(rs.getString("sku_name"));
            productSKU.setSkuSpecs(rs.getString("sku_specs"));
            productSKU.setSellPrice(rs.getFloat("sell_price"));
            productSKU.setMarketPrice(rs.getFloat("market_price"));
            productSKU.setPurchasePrice(rs.getFloat("purchase_price"));
            productSKU.setStockAmount(rs.getInt("stockAmount"));
            return productSKU;
        }
    }
}
